﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Text;
using ConnectLib.DAO;
using MSSQLProvider.DataBase;

namespace ConnectLib.DAL
{
    public class BranchDal : MSSQLDataAccess
    {
        private static BranchDal _instance = new BranchDal();

        public static BranchDal Instance
        {
            get
            {
                return _instance;
            }
        }

        public BranchDal()
        {
        }

        public bool Exist(int branchid)
        {
            if (ExecuteScalar(string.Format("SELECT [BranchId] FROM [Branch] WHERE [BranchId] = {0}", branchid)) != null)
            {
                return true;
            }

            return false;
        }

        public int AddBranch(BranchDao branch)
        {
            try
            {
                if (Exist(branch.BranchId))
                {
                    return 0;
                }

                List<string> arrFielName = new List<string>();
                List<string> arrParamName = new List<string>();
                List<object> arrValue = new List<object>();

                arrFielName.Add("BranchId");
                arrParamName.Add("@BranchId");
                arrValue.Add(branch.BranchId);

                arrFielName.Add("Name");
                arrParamName.Add("@Name");
                arrValue.Add(branch.Name);

                arrFielName.Add("Address");
                arrParamName.Add("@Address");
                arrValue.Add(branch.Address);

                arrFielName.Add("Phone");
                arrParamName.Add("@Phone");
                arrValue.Add(branch.Phone);

                arrFielName.Add("Footer");
                arrParamName.Add("@Footer");
                arrValue.Add(branch.Footer);

                arrFielName.Add("Sort");
                arrParamName.Add("@Sort");
                arrValue.Add(branch.Sort);

                var queryBuider = new StringBuilder();
                queryBuider.Append("INSERT INTO [Branch] (");
                int i = 0;

                for (; i < arrFielName.Count; i++)
                {
                    queryBuider.Append(arrFielName[i]);
                    if (i < arrFielName.Count - 1)
                        queryBuider.Append(",");
                }
                queryBuider.Append(")");
                queryBuider.Append(" VALUES (");
                i = 0;

                for (; i < arrParamName.Count; i++)
                {
                    queryBuider.Append(arrParamName[i]);
                    if (i < arrParamName.Count - 1)
                        queryBuider.Append(",");
                }
                queryBuider.Append(")");

                return ExecuteNonQuery(queryBuider.ToString(), arrParamName, arrValue);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public int UpdateBranch(BranchDao branch, int oldbranchid)
        {
            try
            {
                List<string> arrFielName = new List<string>();
                List<string> arrParamName = new List<string>();
                List<object> arrValue = new List<object>();

                arrFielName.Add("BranchId");
                arrParamName.Add("@BranchId");
                arrValue.Add(branch.BranchId);

                arrFielName.Add("Name");
                arrParamName.Add("@Name");
                arrValue.Add(branch.Name);

                arrFielName.Add("Address");
                arrParamName.Add("@Address");
                arrValue.Add(branch.Address);

                arrFielName.Add("Phone");
                arrParamName.Add("@Phone");
                arrValue.Add(branch.Phone);

                arrFielName.Add("Footer");
                arrParamName.Add("@Footer");
                arrValue.Add(branch.Footer);

                arrFielName.Add("Sort");
                arrParamName.Add("@Sort");
                arrValue.Add(branch.Sort);

                arrFielName.Add("BranchId");
                arrParamName.Add("@oldbranchid");
                arrValue.Add(oldbranchid);
                
                var queryBuider = new StringBuilder();
                queryBuider.Append("UPDATE [Branch] SET ");
                int i = 0;

                for (; i < arrFielName.Count; i++)
                {
                    if (i != arrFielName.Count - 1)
                    {
                        queryBuider.Append(arrFielName[i]);
                        queryBuider.Append("=");
                        queryBuider.Append(arrParamName[i]);
                        if (i < arrFielName.Count - 2)
                            queryBuider.Append(",");
                    }
                    else
                    {
                        queryBuider.Append(" WHERE ");
                        queryBuider.Append(arrFielName[i]);
                        queryBuider.Append("=");
                        queryBuider.Append(arrParamName[i]);
                    }
                }

                return ExecuteNonQuery(queryBuider.ToString(), arrParamName, arrValue);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public string GetBranchName(int branchid)
        {
            string name=string.Empty;
            SqlDataReader dr = null;

            try
            {
                dr = ExecuteReader("SELECT [Name] FROM [Branch] WITH (NOLOCK) WHERE [BranchId] = " + branchid);

                while (dr.Read())
                {
                    name = dr[0].ToString();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if(dr != null)
                    dr.Close();
            }

            return name;
        }

        public int TotalBranchs()
        {
            SqlDataReader dr = null;
            int res = 0;

            try
            {
                dr = ExecuteReader("SELECT COUNT(BranchId) AS [counter] FROM [Branch] WITH (NOLOCK)");
                while (dr.Read())
                {
                    res = (int) dr[0];
                }
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (dr != null)
                {
                    dr.Close();
                }
            }

            return res;
        }

        public List<BranchDao> LoadRange(int startRow, int endRow)
        {
            List<Hashtable> lsHtbl = ExecuteArrayHastable(@"WITH MyOrders AS
                                                        (
                                                            SELECT *, ROW_NUMBER() OVER (ORDER BY [BranchId]) as 'RowNum'
                                                            FROM [Branch] WITH (NOLOCK)" +
                                                              @")
                                                        SELECT * FROM MyOrders
                                                        WHERE RowNum BETWEEN " + startRow + " AND " + endRow);
            List<BranchDao> branchs = new List<BranchDao>(lsHtbl.Count);
            foreach (Hashtable hashtable in lsHtbl)
            {
                branchs.Add(new BranchDao(hashtable));
            }

            return branchs;
        }

        public List<BranchDao> GetFooterBranchs()
        {
            string name = string.Empty;
            List<BranchDao> branchs = null;

            try
            {
                List<Hashtable> htbranchs = ExecuteArrayHastable("SELECT TOP 3 * FROM [Branch] WITH (NOLOCK) WHERE [Footer] = 1 AND ([Sort] > 0 AND [Sort] < 4) ORDER BY [Sort]");

                if (htbranchs != null)
                {
                    branchs = new List<BranchDao>(htbranchs.Count);
                    foreach (Hashtable htitem in htbranchs)
                    {
                        branchs.Add(new BranchDao(htitem));
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return branchs;
        }

        public BranchDao GetBranch(int branchcode)
        {
            string name = string.Empty;
            BranchDao branch = null;

            try
            {
                Hashtable htb = ExecuteHashtable(string.Format("SELECT * FROM [Branch] WITH (NOLOCK) WHERE [BranchId] = {0}", branchcode));

                if (htb != null)
                {
                    branch = new BranchDao(htb);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return branch;
        }

        public List<BranchDao> GetBranchs()
        {
            string name = string.Empty;
            List<BranchDao> branchs = null;

            try
            {
                List<Hashtable> htbranchs = ExecuteArrayHastable("SELECT TOP 8 * FROM [Branch] WITH (NOLOCK)");

                if (htbranchs != null)
                {
                    branchs=new List<BranchDao>(htbranchs.Count);
                    foreach (Hashtable htitem in htbranchs)
                    {
                        branchs.Add(new BranchDao(htitem));
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return branchs;
        }

        public bool DeleteIBranch(string branchid)
        {
            return ExecuteNonQuery(string.Format("DELETE FROM [Branch] WHERE BranchId = {0}", branchid));
        }

        public bool DeleteBranchs(string branchs)
        {
            return ExecuteNonQuery(string.Format("DELETE FROM [Branch] WHERE BranchId in ({0})", branchs));
        }
    }
}
